# %%
import datetime,os
import pandas as pd
import warnings

warnings.simplefilter(action='ignore', category=FutureWarning)

today = datetime.date.today()
# Dataframe设置不显示小数点
pd.set_option('display.precision', 0)
locations_order = [
    '广州', '深圳', '东莞', '佛山', '中山', '惠州', '珠海', '江门', '汕头', '湛江', '揭阳', '肇庆',
    '韶关', '清远', '茂名', '梅州', '潮州', '阳江', '河源', '汕尾', '云浮', '科学城', '松山湖', '白云北',
    '知识城'
]
znv_list = {
    '广州': ['212.11.36.214', 'sa', 'zxm,10', 'zxm10lsc'],
    '深圳': ['212.11.82.92', 'sa', 'zxm10', 'zxm10lsc'],
    '东莞': ['212.11.120.124', 'sa', 'zxm10ZXM10', 'zxm10lsc'],
    '东莞基站': ['212.11.120.127', 'sa', 'zxm,10', 'zxm10lsc'],
    '中山': ['212.11.172.10', 'sa', 'zxm,10', 'zxm10lsc'],
    '珠海': ['212.11.184.155', 'sa', 'zxm,10', 'zxm10lsc'],
    '江门': ['212.11.207.8', 'sa', 'zxm,10', 'zxm10lsc'],
    '汕头': ['212.11.193.78', 'sa', 'zxm10', 'zxm10lsc'],
    '汕头基站': ['212.11.193.76', 'sa', 'zxm10', 'zxm10lsc'],
    '湛江': ['212.11.232.77', 'sa', 'zxm,10', 'zxm10lsc'],
    '肇庆': ['212.12.6.76', 'sa', 'zxm10', 'zxm10lsc'],
    '清远': ['212.12.0.154', 'sa', 'zxm10', 'zxm10lsc'],
    '茂名': ['212.11.246.77', 'sa', 'zxm,10', 'zxm10lsc'],
    '梅州': ['212.12.27.76', 'sa', 'zxm10', 'zxm10lsc'],
    '阳江': ['212.11.252.80', 'sa', 'zxm,10', 'zxm10lsc'],
    '河源': ['212.12.35.76', 'sa', 'zxm,10', 'zxm10lsc'],
    '汕尾': ['212.12.38.77', 'sa', 'zxm,10', 'zxm10lsc'],
    '云浮': ['212.12.23.77', 'sa', 'zxm10', 'zxm10lsc']
}
znv = f''' SELECT  dev.device_cid , sig.mete_cid,alm.*    FROM zxm10_his.[dbo].[nms_his_devicealarm] alm 
LEFT JOIN  zxm10_cfg.[dbo].[nms_cfg_device] dev  on alm.device_id = dev.device_id and alm.lsc_id = dev.lsc_id 
LEFT JOIN  zxm10_cfg.[dbo].[nms_cfg_telesignal] sig  on alm.mete_id = sig.mete_id  and dev.device_id = sig.device_id and dev.lsc_id = sig.lsc_id 
LEFT JOIN  zxm10_bas.[dbo].[nms_bas_explain] t on alm.explain_type = t.explain_type and alm.alarm_state = t.signal_value
where alm.mask_type = 0  and alm.clear_time is null  
'''
Weidi_list = {
    '广州': ['212.11.36.104', 'sa', 'siteweb@2012', 'siteweb'],
    '中山': ['212.11.172.149', 'sa', 'SiteWeb@2019', 'siteweb'],
    '惠州': ['212.11.218.200', 'sa', 'siteweb@2011', 'siteweb'],
    '珠海': ['212.11.184.154', 'sa', 'siteweb@2011', 'siteweb'],
    '江门': ['212.11.207.7', 'sa', 'siteweb@2011', 'siteweb'],
    '汕头': ['212.11.194.76', 'sa', 'siteweb@2019', 'SiteWeb'],
    '湛江': ['212.11.232.254', 'sa', 'siteweb@2011', 'siteWeb2'],
    '揭阳': ['212.11.240.76', 'sa', 'siteweb@2012', 'siteweb'],
    '肇庆': ['212.12.6.77', 'sa', 'Sitewei@2020', 'siteweb'],
    '韶关': ['212.12.12.104', 'sa', 'siteweb@2011', 'siteweb'],
    '清远': ['212.12.0.77', 'sa', 'siteweb@2011', 'siteweb'],
    '梅州': ['212.12.27.77', 'sa', 'siteweb@2019', 'siteweb'],
    '潮州': ['212.12.18.76', 'sa', 'siteweb@2011', 'siteweb'],
    '阳江': ['212.11.253.80', 'sa', 'Sitewei@YJ2020', 'siteWeb1'],
    '汕尾': ['212.12.38.76', 'sa', 'siteweb@2019', 'siteweb']
}
Weidi = f'''SELECT count(Count) AS "告警总数"
FROM (
    SELECT SequenceId, COUNT(SequenceId) AS Count
    FROM TBL_AlarmChange
    WHERE StartTime >= '{today} 00:00:00' AND StartTime <= '{today} 20:00:00'
    GROUP BY SequenceId
) AS SubQuery;
 '''


# %%
def sql_server(server, sql,name=''):
    import pymssql

    conn = pymssql.connect(server[0],
                           server[1],
                           server[2],
                           server[3],
                           tds_version="7.0")
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    conn.close()
    if name != 'znv':
        if len(result) == 0:
            return 0
        return result[0][0]
    else:
        return len(result)


# %%
def main():
    sql_znv = {}
    sql_Weidi = {}
    for i in znv_list:
        try:
            if i[:2] in sql_znv:
                sql_znv[i[:2]] += sql_server(znv_list[i], znv,'znv')
            else:
                sql_znv[i] = sql_server(znv_list[i], znv)
        except:
            print(f'{i}数据库连接失败')
    for i in Weidi_list:
        try:
            sql_Weidi[i] = sql_server(Weidi_list[i], Weidi)
        except:
            print(f'{i}数据库连接失败')
    sql_out = pd.DataFrame([sql_znv, sql_Weidi], index=['数据库C接口', '数据库维谛接口']).T
    sql_out = sql_out.reindex(index=locations_order)

    # 将NAN空值设为0
    sql_out.fillna('/', inplace=True)
    return sql_out
def znv_alarm():
    sql_znv = {}
    for i in znv_list:
        try:
            sql_znv[i] = sql_server(znv_list[i], znv,'znv')
        except:
            print(f'{i}数据库连接失败')
    sql_out = pd.DataFrame([sql_znv], index=['数据库C接口']).T
    sql_out = sql_out.reindex(index=locations_order)
    # 将NAN空值设为0
    sql_out.fillna('/', inplace=True)
    return sql_out

# %%
if __name__ == '__main__':
    aa = znv_alarm()
    print(aa)
    aa.to_excel(os.path.join(os.path.expanduser("~"), 'Desktop') +
                '\\数据库.xlsx',
                index=True,
                header=True)
